package com.dy.yunying.biz.dao.clickhouse3399.impl;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.dy.yunying.api.constant.Constant;
import com.dy.yunying.api.dto.AdOverviewDto2;
import com.dy.yunying.api.vo.AdDataAnalysisVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;

/**
 * TODO
 *
 * @Author: hjl
 * @Date: 2021/6/24 13:44
 */
@Slf4j
@Component
public class AdAnalysisDao {

	@Resource(name = "clickhouseTemplate")
	private JdbcTemplate clickhouseTemplate;

	public List<AdDataAnalysisVO> list(AdOverviewDto2 req) {
		StringBuilder sql = new StringBuilder();


		Long rsTime = req.getRsTime();
		Long reTime = req.getReTime();
		String period = req.getPeriod();
		String queryColumn = convertQueryColumn(req.getQueryColumn());

		Integer isSys = req.getIsSys();

		final String userIds = req.getUserIds();
		final String adAccounts = req.getAdAccounts();

		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String parentchl = req.getParentchl();
		final String chl = req.getChl();
		final String appchl = req.getAppchl();
		final String parentchlArr = req.getParentchlArr();
		final String deptIdArr = req.getDeptIdArr();
		final String userGroupIdArr = req.getUserGroupIdArr();
		final String investorArr = req.getInvestorArr();
		final String adidName = req.getAdidName();
		final String adidArr = req.getAdidArr();
		final String advertiserIdArr = req.getAdvertiserIdArr();
		final String agentIdArr = req.getAgentIdArr();
		final String convertArr = req.getConvertArr();
		final String deepConvertArr = req.getDeepConvertArr();
		final String convertDataTypeArr = req.getConvertDataTypeArr();

		// 通用筛选条件
		StringBuffer commCondSB = new StringBuffer("");

		if (os != null) {
			commCondSB.append("                     AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append("                     AND pgid IN (").append(pgidArr).append(")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append("                     AND gameid IN (").append(gameidArr).append(")");
		}
		if (StringUtils.isNotBlank(parentchl)) {
			commCondSB.append("                     AND parentchl = '").append(parentchl).append("'");
		}
		if (StringUtils.isNotBlank(chl)) {
			commCondSB.append("                     AND chl = '").append(chl).append("'");
		}
		if (StringUtils.isNotBlank(appchl)) {
			commCondSB.append("                     AND appchl = '").append(appchl).append("'");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append("                     AND parentchl IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(deptIdArr)) {
			commCondSB.append("                     AND deptId IN (").append(deptIdArr).append(")");
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			commCondSB.append("                     AND userGroupId IN (").append(userGroupIdArr).append(")");
		}
		if (StringUtils.isNotBlank(investorArr)) {
			commCondSB.append("                     AND investor IN (").append(investorArr).append(")");
		}
		if (StringUtils.isNotBlank(adidName)) {
			commCondSB.append("                     AND adidName LIKE CONCAT('%', '").append(adidName).append("', '%')");
		}
		if (StringUtils.isNotBlank(adidArr)) {
			commCondSB.append("                     AND adid IN ('").append(adidArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(advertiserIdArr)) {
			commCondSB.append("                     AND advertiserid IN ('").append(advertiserIdArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(agentIdArr)) {
			commCondSB.append("                     AND advertiserid IN (SELECT toString(advertiser_id) FROM odsmysql_ad_account_agent t where t.agent_id in (" + agentIdArr + ") and toYYYYMMDD(t.effective_time) <= toYYYYMMDD(now()) and toYYYYMMDD(t.invalid_time) >= toYYYYMMDD(now()))");
		}
		if (StringUtils.isNotBlank(convertArr)) {
			commCondSB.append("     and convertName IN ('").append(convertArr.replaceAll(Constant.COMMA, "','")).append("')");
		}
		if ("empty".equals(deepConvertArr)) {
			commCondSB.append("     and (deepConvert = '' OR deepConvert IS NULL)");
		} else if (StringUtils.isNotBlank(deepConvertArr)) {
			commCondSB.append("     and deepConvert = '").append(deepConvertArr).append("'");
		}
		if (StringUtils.isNotBlank(convertDataTypeArr)) {
			commCondSB.append("     and convertDataType = '").append(convertDataTypeArr).append("'");
		}

		String commCond = commCondSB.toString();
		log.info("commCond : [{}]", commCond);

		sql.append(" SELECT");
		sql.append("     period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("     ").append(queryColumn).append(",");
		}
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("adid")) {
			sql.append("     budget,");
			sql.append("     adidName,");
			sql.append("     ctype,");
			sql.append("     campaignId,");
			sql.append("     campaignName,");
			sql.append("     startTime,");
			sql.append("     endTime,");
			sql.append("     convertId,");
			sql.append("     createTime,");
			sql.append("     updateTime,");
			sql.append("     unionVideoType,");
			sql.append("     inventoryType,");
			sql.append("     deliveryRange,");
			sql.append("     pricing,");
			sql.append("     advertid,");
			sql.append("     adAccountName,");
			sql.append("     status,");
			sql.append("     optStatus,");
			sql.append("     adgroupId,");
			sql.append("     convertName,");
			sql.append("     convertDescri,");
			sql.append("     deepConvert,");
			sql.append("     deepConvertDescri,");
		}
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("advertiserid") && !queryColumn.contains("adid")) {
			sql.append("     advertiserid advertid,");
			sql.append("     oaa.advertiser_name adAccountName,");
		}
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("deptId")) {
			sql.append("     IFNULL(osd.name, '') deptName,");
		}

		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("userGroupId")) {
			sql.append("     osdg.name userGroupName,");
		}
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("investor")) {
			sql.append("     IFNULL(osu.real_name, '') investorName,");
		}
		sql.append("     IFNULL(cost, toDecimal32(0, 3)) cost,");
		sql.append("     IFNULL(rudecost, toDecimal32(0, 3)) rudecost,");
		sql.append("     IFNULL(shownums, 0) shownums,");
		sql.append("     IFNULL(clicknums, 0) clicknums,");
		sql.append("     IFNULL(uuidnums, 0) uuidnums,");
		sql.append("     IFNULL(usrnamenums, 0) usrnamenums,");
		sql.append("     IFNULL(retention2, 0) retention2,");
		sql.append("     IFNULL(retention3, 0) retention3,");
		sql.append("     IFNULL(retention4, 0) retention4,");
		sql.append("     IFNULL(retention5, 0) retention5,");
		sql.append("     IFNULL(retention6, 0) retention6,");
		sql.append("     IFNULL(retention7, 0) retention7,");
		sql.append("     IFNULL(retention8, 0) retention8,");
		sql.append("     IFNULL(paydeviceAll, 0) paydeviceAll,");
		sql.append(req.getShowRatio()).append("  showRatio,");
		sql.append("     IFNULL(worth1, toDecimal32(0, 3)) worth1,");
		sql.append("     IFNULL(newdevicesharfee, toDecimal32(0, 3)) newdevicesharfee,");
		sql.append("     IFNULL(weeksharfee, toDecimal32(0, 3)) weeksharfee,");
		sql.append("     IFNULL(monthsharfee, toDecimal32(0, 3)) monthsharfee,");
		sql.append("     IFNULL(totalPaysharfee, toDecimal32(0, 3)) totalPaysharfee,");
		sql.append("     IFNULL(paydevice1, 0) paydevice1,");
		sql.append("     IFNULL(worth7, toDecimal32(0, 3)) worth7,");
		sql.append("     IFNULL(paydevice7, 0) paydevice7,");
		sql.append("     IFNULL(worth30, toDecimal32(0, 3)) worth30,");
		sql.append("     IFNULL(paydevice30, 0) paydevice30,");
		sql.append("     IFNULL(userfeeAll, toDecimal32(0, 3)) userfeeAll,");
		sql.append("     IFNULL(givemoneyAll, toDecimal32(0, 3)) givemoneyAll,");
		sql.append("     IFNULL(activeNum, 0) activeNum,");
		sql.append("     IFNULL(activeLoginNum, 0) activeLoginNum,");
		sql.append("     IFNULL(paydevice, 0) paydevice,");
		sql.append("     IFNULL(userfee, toDecimal32(0, 3)) userfee,");
		sql.append("     IFNULL(activesharfee, toDecimal32(0, 3)) activesharfee,");
		sql.append("     IFNULL(givemoney, toDecimal32(0, 3)) givemoney");
		sql.append(" FROM (");
		//              广告维度
		sql.append("     SELECT");
		sql.append("         period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("         ").append(queryColumn).append(",");
		}
		sql.append("         cost,");
		sql.append("         rudecost,");
		sql.append("         shownums,");
		sql.append("         clicknums,");
		sql.append("         uuidnums,");
		sql.append("         usrnamenums,");
		sql.append("         retention2,");
		sql.append("         retention3,");
		sql.append("         retention4,");
		sql.append("         retention5,");
		sql.append("         retention6,");
		sql.append("         retention7,");
		sql.append("         retention8,");
		sql.append("         paydeviceAll,");
		sql.append("         worth1,");
		sql.append("         newdevicesharfee,");
		sql.append("         weeksharfee,");
		sql.append("         monthsharfee,");
		sql.append("         totalPaysharfee,");
		sql.append("         paydevice1,");
		sql.append("         worth7,");
		sql.append("         paydevice7,");
		sql.append("         worth30,");
		sql.append("         paydevice30,");
		sql.append("         userfeeAll,");
		sql.append("         givemoneyAll,");
		sql.append("         activeNum,");
		sql.append("         activeLoginNum,");
		sql.append("         paydevice,");
		sql.append("         activesharfee,");
		sql.append("         userfee,");
		sql.append("         givemoney");
		sql.append("     FROM (");
		sql.append("         SELECT");
		sql.append("             period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("             ").append(queryColumn).append(",");
		}
		sql.append("             uuidnums,");
		sql.append("             usrnamenums,");
		sql.append("             retention2,");
		sql.append("             retention3,");
		sql.append("             retention4,");
		sql.append("             retention5,");
		sql.append("             retention6,");
		sql.append("             retention7,");
		sql.append("             retention8,");
		sql.append("             paydeviceAll,");
		sql.append("             worth1,");
		sql.append("                 newdevicesharfee,");
		sql.append("                 weeksharfee,");
		sql.append("                 monthsharfee,");
		sql.append("                 totalPaysharfee,");
		sql.append("             paydevice1,");
		sql.append("             worth7,");
		sql.append("             paydevice7,");
		sql.append("             worth30,");
		sql.append("             paydevice30,");
		sql.append("             userfeeAll,");
		sql.append("             givemoneyAll,");
		sql.append("             activeNum,");
		sql.append("             activeLoginNum");
		sql.append("         FROM (");
		sql.append("             SELECT");
		sql.append("                 period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("                 ").append(queryColumn).append(",");
		}
		sql.append("                 uuidnums,");
		sql.append("                 usrnamenums,");
		sql.append("                 retention2,");
		sql.append("                 retention3,");
		sql.append("                 retention4,");
		sql.append("                 retention5,");
		sql.append("                 retention6,");
		sql.append("                 retention7,");
		sql.append("                 retention8");
		sql.append("             FROM (");
		//                          新增设备  累计注册账号的设备数");
		sql.append("                 SELECT");
		sql.append("                     period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("                     ").append(queryColumn).append(",");
		}
		sql.append("                     COUNT(DISTINCT reg.kid) uuidnums,");
		sql.append("                     COUNT(DISTINCT ud.dr_kid) usrnamenums");
		sql.append("                 from (");
		//                              新增设备及其广告归因");
		sql.append("                     SELECT");
		sql.append("                         a.day day,");
		sql.append("                         parseDateTimeBestEffort(toString(day)) dayDate,");
		sql.append("                         toString(toYear(dayDate)) yearStr,");
		sql.append("                         toMonth(dayDate) monthInt,");
		sql.append("                         toYearWeek(dayDate, 3) weekInt,");
		sql.append("                         concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr,");
		sql.append("                         concat(toString(weekInt), '周') AS weekStr,");
		sql.append("                         ").append(period).append(" AS period,");
		sql.append("                         a.pgid pgid,a.gameid gameid,a.os os,a.parentchl parentchl,a.chl chl,a.appchl appchl,");
		sql.append("                         wpc.manage investor,"); // 投放人
		sql.append("                     	 IFNULL(osu.dept_group_id,0) userGroupId,"); // 组别
		sql.append("                         osu.dept_id deptId,"); // 部门
		sql.append("                         (case when ad.adid is not null THEN ad.adid else '' end) as adid,");
		sql.append("                         (case when ad.adname is not null THEN ad.adname else '' end) as adidName,");
		sql.append("                         (case when ad.adaccount is not null THEN ad.adaccount else '' end) as advertiserid,");
		sql.append("                         (case when ad.ctype is not null THEN ad.ctype else 0 end) as ctype,");
		sql.append("                         a.uuid uuid,a.kid kid,a.receivetime receivetime,ad.advert_id advert_id ,vad.convert_name convertName, vad.deep_convert deepConvert, vad.convert_data_type convertDataType ");
		sql.append("                     from thirty_game_device_reg a");
		sql.append("                     LEFT JOIN v_thirty_ad_device ad");
		sql.append("                     on a.uuid = ad.uuid");
		sql.append("                     and a.pgid = ad.pgid");
		sql.append("                     and a.day = ad.`day`");
		sql.append("                     LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                     ON wpc.isdelete = 0");
		sql.append("                     AND wpc.parent_code = a.parentchl");
		sql.append("                     AND wpc.chncode = a.chl");
		sql.append("                     LEFT JOIN odsmysql_sys_user osu");
		sql.append("                     ON wpc.manage = osu.user_id");
		sql.append("                     LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                     ON ad.adid = vad.adid");
		sql.append("                     AND ad.ctype = vad.ctype");
		sql.append("                     where 1=1");
		sql.append("                     and a.day >= ").append(rsTime);
		sql.append("                     and a.day <= ").append(reTime);
		sql.append("                 ) reg");
		sql.append("                 LEFT JOIN (");
//        sql.append("                     -- 新增设备上注册的账号");
		sql.append("                     select");
		sql.append("                         ur.kid ur_kid,");
		sql.append("                         argMax(dr.kid, dr.receivetime) dr_kid,");
//        sql.append("             --            argMax(ur.day, dr.receivetime) day,");
//        sql.append("             --            argMax(ur.uuid, dr.receivetime) uuid,");
		sql.append("                         argMax(ur.usrname, dr.receivetime) usrname");
//        sql.append("             --            argMax(ur.gameid, dr.receivetime) gameid,");
//        sql.append("             --            argMax(dr.parentchl, dr.receivetime) parentchl,");
//        sql.append("             --            argMax(dr.chl, dr.receivetime) chl,");
//        sql.append("             --            argMax(dr.appchl, dr.receivetime) appchl");
		sql.append("                     from v_game_account_reg ur");
		sql.append("                     INNER join odsmysql_wan_game g");
		sql.append("                     on ur.gameid = g.id");
		sql.append("                     INNER join thirty_game_device_reg dr");
		sql.append("                     on ur.uuid = dr.uuid");
		sql.append("                     and g.pgid = dr.pgid");
		sql.append("                     where 1=1");
		sql.append("                     and  ur.receivetimes >= dr.receivetime");
		sql.append("                     and dr.day >= ").append(rsTime);
		sql.append("                     and dr.day <= ").append(reTime);
		sql.append("                     group by ur.kid");
		sql.append("                 ) ud");
		sql.append("                 on reg.kid = ud.dr_kid");
		sql.append("                 where 1=1");
		if (isSys == null || isSys != 1) {
//          sql.append("                     -- 渠道权限");
//          sql.append("                     -- 自己及管理的账号");
			sql.append("                     AND investor IN (").append(userIds).append(")");
		}
//        sql.append("                 -- 筛选条件");
		sql.append(commCond);
		sql.append("                 GROUP BY period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append("             ) t001");
		sql.append("             FULL JOIN (");
//        sql.append("                 -- 留存（有登录）");
		sql.append("                 SELECT");
		sql.append("                     period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("                     ").append(queryColumn).append(",");
		}
		sql.append("                     MAX(niff) dayDiff,");
		sql.append("                     (case when dayDiff < 1 then NULL else IFNULL(uniqExact(activeuuid2), 0) end) retention2,");
		sql.append("                     (case when dayDiff < 2 then NULL else IFNULL(uniqExact(activeuuid3), 0) end) retention3,");
		sql.append("                     (case when dayDiff < 3 then NULL else IFNULL(uniqExact(activeuuid4), 0) end) retention4,");
		sql.append("                     (case when dayDiff < 4 then NULL else IFNULL(uniqExact(activeuuid5), 0) end) retention5,");
		sql.append("                     (case when dayDiff < 5 then NULL else IFNULL(uniqExact(activeuuid6), 0) end) retention6,");
		sql.append("                     (case when dayDiff < 6 then NULL else IFNULL(uniqExact(activeuuid7), 0) end) retention7,");
		sql.append("                     (case when dayDiff < 7 then NULL else IFNULL(uniqExact(activeuuid8), 0) end) retention8");
		sql.append("                 from (");
		sql.append("                     select");
		sql.append("                         argMax(reg.day, reg.receivetime) regDay,");
		sql.append("                         argMax(ul.day, reg.receivetime) activeDay,");
		sql.append("                         regDay AS day,");
		sql.append("                         parseDateTimeBestEffort(toString(day)) dayDate,");
		sql.append("                         toString(toYear(dayDate)) yearStr,");
		sql.append("                         toMonth(dayDate) monthInt,");
		sql.append("                         toYearWeek(dayDate, 3) weekInt,");
		sql.append("                         concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr,");
		sql.append("                         concat(toString(weekInt), '周') AS weekStr,");
		sql.append("                         ").append(period).append(" AS period,");
		sql.append("                         argMax(reg.pgid, reg.receivetime) pgid,");
		sql.append("                         argMax(reg.gameid, reg.receivetime) AS gameid,");
		sql.append("                         argMax(reg.os, reg.receivetime) AS os,");
		sql.append("                         argMax(reg.parentchl, reg.receivetime) parentchl,");
		sql.append("                         argMax(reg.chl, reg.receivetime) chl,");
		sql.append("                         argMax(reg.appchl, reg.receivetime) appchl,");
		sql.append("                         argMax(wpc.manage, reg.receivetime) investor,"); // 投放人
		sql.append("                         IFNULL(argMax(osu.dept_group_id, reg.receivetime),0) userGroupId,"); // 组别
		sql.append("                         argMax(osu.dept_id, reg.receivetime) deptId,"); // 部门
		sql.append("                         argMax(ad.adid, reg.receivetime) adidTmp,");
		sql.append("                         argMax(ad.adname, reg.receivetime) adidName,");
		sql.append("                         IFNULL(argMax(ad.adaccount, reg.receivetime),'') advertiserid,");
		sql.append("                         IFNULL(argMax(ad.ctype, reg.receivetime),0) ctype,");
		sql.append("                         (case when adidTmp is not null THEN adidTmp else '' end) as adid,");
		sql.append("                         ul.kid ulkid,");
		sql.append("                         argMax(reg.kid, reg.receivetime) regkid,");
		sql.append("                         dateDiff('day', parseDateTimeBestEffort(toString(regDay)),  today() ) as niff, ");// --距离今天多少天
		sql.append("                         dateDiff('day', parseDateTimeBestEffort(toString(regDay)), parseDateTimeBestEffort(toString(activeDay))) as diff,");
		sql.append("                         argMax(ul.uuid, reg.receivetime) AS uuid,");
		sql.append("                         (case when diff = 1 then regkid else null  end) activeuuid2,");
		sql.append("                         (case when diff = 2 then regkid else null  end) activeuuid3,");
		sql.append("                         (case when diff = 3 then regkid else null  end) activeuuid4,");
		sql.append("                         (case when diff = 4 then regkid else null  end) activeuuid5,");
		sql.append("                         (case when diff = 5 then regkid else null  end) activeuuid6,");
		sql.append("                         (case when diff = 6 then regkid else null  end) activeuuid7,");
		sql.append("                         (case when diff = 7 then regkid else null  end) activeuuid8,");
		sql.append("                         argMax(ad.advert_id, reg.receivetime) advert_id ,argMax(vad.convert_name, reg.receivetime)  convertName, argMax(vad.deep_convert, reg.receivetime) deepConvert, argMax(vad.convert_data_type, reg.receivetime) convertDataType ");
		sql.append("                     from user_login ul");
		sql.append("                     INNER join odsmysql_wan_game g");
		sql.append("                     on ul.gameid = g.id");
		sql.append("                     INNER join thirty_game_device_reg reg");
		sql.append("                     on ul.uuid = reg.uuid");
		sql.append("                     and g.pgid = reg.pgid");
		sql.append("                     LEFT JOIN v_thirty_ad_device ad");
		sql.append("                     on reg.uuid = ad.uuid");
		sql.append("                     and reg.pgid = ad.pgid");
		sql.append("                     and reg.`day` = ad.`day`");
		sql.append("                     LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                     ON wpc.isdelete = 0");
		sql.append("                     AND wpc.parent_code = reg.parentchl");
		sql.append("                     AND wpc.chncode = reg.chl");
		sql.append("                     LEFT JOIN odsmysql_sys_user osu");
		sql.append("                     ON wpc.manage = osu.user_id");
		sql.append("                     LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                     ON ad.adid = vad.adid");
		sql.append("                     AND ad.ctype = vad.ctype");
		sql.append("                     where 1=1");
		sql.append("                     and ul.`day`>= ").append(rsTime);
		sql.append("                     and reg.day>= ").append(rsTime);
		sql.append("                     and reg.day<= ").append(reTime);
		sql.append("                     and ul.receivetime >= reg.receivetime");
		sql.append("                     GROUP BY ul.kid");
		sql.append("                 ) a");
		sql.append("                 where 1=1");
		if (isSys == null || isSys != 1) {
//          sql.append("                     -- 渠道权限");
//          sql.append("                     -- 自己及管理的账号");
			sql.append("                     AND investor IN (").append(userIds).append(")");
		}
//        sql.append("                 -- 筛选条件");
		sql.append(commCond);
		sql.append("                 GROUP BY period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append("             ) t002");
		sql.append("             USING (period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append(" )");
		sql.append("         ) t010");
		sql.append("         FULL JOIN (");
		sql.append("             SELECT");
		sql.append("                 period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("                 ").append(queryColumn).append(",");
		}
		sql.append("                 paydeviceAll,");
		sql.append("                 worth1,");
		sql.append("                 newdevicesharfee,");
		sql.append("                 weeksharfee,");
		sql.append("                 monthsharfee,");
		sql.append("                 totalPaysharfee,");
		sql.append("                 paydevice1,");
		sql.append("                 worth7,");
		sql.append("                 paydevice7,");
		sql.append("                 worth30,");
		sql.append("                 paydevice30,");
		sql.append("                 userfeeAll,");
		sql.append("                 givemoneyAll,");
		sql.append("                 activeNum,");
		sql.append("                 activeLoginNum");
		sql.append("             FROM ("); // -- 新增设备N日付费
		sql.append("                 select");
		sql.append("                     period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("                     ").append(queryColumn).append(",");
		}
		sql.append("                     COUNT(DISTINCT regkid) paydeviceAll, ");// -- 新增设备累计付费设备数
		sql.append("                     SUM(worth1) worth1,");
		sql.append(" SUM(sharfee1) newdevicesharfee,   ----新增充值金额分成后 \n ");
		sql.append(" SUM(sharfee7) weeksharfee,   ----当周充值金额分成后  \n ");
		sql.append(" SUM(sharfee30) monthsharfee,   ----当月充值金额 分成后 \n ");
		sql.append(" SUM(sharfee) totalPaysharfee,   ----累计充值金额 分成后 \n ");
		sql.append("                     COUNT(DISTINCT paykid1) paydevice1,");
		sql.append("                     SUM(worth7) worth7,");
		sql.append("                     COUNT(DISTINCT paykid7) paydevice7,");
		sql.append("                     SUM(worth30) worth30,");
		sql.append("                     COUNT(DISTINCT paykid30) paydevice30,");
		sql.append("                     SUM(fee) userfeeAll,");
		sql.append("                     SUM(givemoney) givemoneyAll");
		sql.append("                 from (");
//        sql.append("                  -- 新增设备充值");
		sql.append("                     select");
		sql.append("                         argMax(reg.day, reg.receivetime) regDay,");
		sql.append("                         regDay AS day,");
		sql.append("                         parseDateTimeBestEffort(toString(day)) dayDate,");
		sql.append("                         toString(toYear(dayDate)) yearStr,");
		sql.append("                         toMonth(dayDate) monthInt,");
		sql.append("                         toYearWeek(dayDate, 3) weekInt,");
		sql.append("                         concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr,");
		sql.append("                         concat(toString(weekInt), '周') AS weekStr,");
		sql.append("                         ").append(period).append(" AS period,");
		sql.append("                         argMax(reg.pgid, reg.receivetime) pgid,");
		sql.append("                         argMax(reg.gameid, reg.receivetime) AS gameid,");
		sql.append("                         argMax(reg.os, reg.receivetime) AS os,");
		sql.append("                         argMax(reg.parentchl, reg.receivetime) parentchl,");
		sql.append("                         argMax(reg.chl, reg.receivetime) chl,");
		sql.append("                         argMax(reg.appchl, reg.receivetime) appchl,");
		sql.append("                         argMax(wpc.manage, reg.receivetime) investor,"); // 投放人
		sql.append("                         IFNULL(argMax(osu.dept_group_id, reg.receivetime),0) userGroupId,"); // 组别
		sql.append("                         argMax(osu.dept_id, reg.receivetime) deptId,"); // 部门
		sql.append("                         argMax(ad.adid, reg.receivetime) adidTmp,");
		sql.append("                         argMax(ad.adname, reg.receivetime) adidName,");
		sql.append("                         IFNULL(argMax(ad.adaccount, reg.receivetime),'') advertiserid,");
		sql.append("                         IFNULL(argMax(ad.ctype, reg.receivetime),0) ctype,");
		sql.append("                         (case when adidTmp is not null THEN adidTmp else '' end) as adid,");
		sql.append("                         rc.kid rckid,");
		sql.append("                         argMax(reg.kid, reg.receivetime) regkid,");
		sql.append("                         argMax(rc.day, reg.receivetime) payday,");
		sql.append("                         parseDateTimeBestEffort(toString(payday)) paydayDate,");
		sql.append("                         toString(toYear(paydayDate)) payYearStr,");
		sql.append("                         toMonth(paydayDate) payMonthInt,");
		sql.append("                         toYearWeek(paydayDate, 3) payWeekInt,");
		sql.append("                         concat(payYearStr, '-', IF(10>payMonthInt, '0', ''), toString(payMonthInt), '月') AS payMonthStr,");
		sql.append("                         dateDiff('day', parseDateTimeBestEffort(toString(day)),  today() ) as  cur_diff, ");// --距离今天多少天
		sql.append("                         dateDiff('day', parseDateTimeBestEffort(toString(day)), parseDateTimeBestEffort(toString(payday))) as pay_diff,");
		sql.append("                         argMax(rc.uuid, reg.receivetime) AS payuuid,");
		sql.append("                         argMax(rc.fee, reg.receivetime) fee,");
		sql.append("                         argMax(rc.givemoney, reg.receivetime) givemoney,");
		sql.append(" argMax(rc.sharfee,reg.receivetime) sharfee, \n ");
		sql.append("                         (case when pay_diff = 0 then fee + givemoney else 0  end) worth1,");
		sql.append(" (case when pay_diff = 0 then sharfee else 0 end) sharfee1, \n ");
		sql.append("                         (case when pay_diff = 0 then regkid else null  end) paykid1,");
		sql.append("                         (case when weekInt = payWeekInt then fee + givemoney   else 0  end) worth7,");
		sql.append(" (case when weekInt = payWeekInt then sharfee else 0 end) sharfee7, \n ");
		sql.append("                         (case when weekInt = payWeekInt then regkid else null  end) paykid7,");
		sql.append("                         (case when monthStr = payMonthStr then fee + givemoney   else 0  end) worth30,");
		sql.append(" (case when monthStr = payMonthStr then sharfee else 0 end) sharfee30, \n ");
		sql.append("                         (case when monthStr = payMonthStr then regkid else null  end) paykid30,");
		sql.append("                         argMax(rc.createtime, reg.receivetime) createtime, argMax(vad.convert_name, reg.receivetime) convertName, argMax(vad.deep_convert, reg.receivetime) deepConvert, argMax(vad.convert_data_type, reg.receivetime) convertDataType ");
		sql.append("                     from v_original_user_recharge_share rc");
		sql.append("                     LEFT join thirty_game_device_reg reg");
		sql.append("                     on rc.uuid=reg.uuid");
		sql.append("                     and rc.pgid = reg.pgid");
		sql.append("                     LEFT JOIN v_thirty_ad_device ad");
		sql.append("                     on reg.uuid = ad.uuid");
		sql.append("                     and reg.day = ad.day");
		sql.append("                     and reg.pgid = ad.pgid");
		sql.append("                     LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                     ON wpc.isdelete = 0");
		sql.append("                     AND wpc.parent_code = reg.parentchl");
		sql.append("                     AND wpc.chncode = reg.chl");
		sql.append("                     LEFT JOIN odsmysql_sys_user osu");
		sql.append("                     ON wpc.manage = osu.user_id");
		sql.append("                     LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                     ON ad.adid = vad.adid");
		sql.append("                     AND ad.ctype = vad.ctype");
		sql.append("                     where 1=1 and");
		sql.append("                     reg.`day` >= ").append(rsTime);
		sql.append("                     and reg.`day`<= ").append(reTime);
		sql.append("                     AND rc.`day` >= reg.`day`");
		sql.append("                     group by rc.kid");
		sql.append("                 ) a");
		sql.append("                 where 1=1");
		if (isSys == null || isSys != 1) {
//          sql.append("                     -- 渠道权限");
//          sql.append("                     -- 自己及管理的账号");
			sql.append("                     AND investor IN (").append(userIds).append(")");
		}
//        sql.append("                 -- 筛选条件");
		sql.append(commCond);
		sql.append("                 GROUP BY period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append("             ) t003");
		sql.append("             FULL JOIN (");
//        sql.append("                 -- 活跃设备数 当天有登录活跃设备数");
		sql.append("                 SELECT");
		sql.append("                     period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("                     ").append(queryColumn).append(",");
		}
		sql.append("                     uniqExact(regkid) activeNum,");
		sql.append("                     uniqExact(IF(login_uuid IS NULL, null, regkid)) activeLoginNum");
		sql.append("                 from (");//-- 活跃
		sql.append("                     select");
		sql.append("                         argMax(reg.day, reg.receivetime) regDay,");
		sql.append("                         argMax(ub.day, reg.receivetime) activeDay,");
		sql.append("                         activeDay AS day,");
		sql.append("                         parseDateTimeBestEffort(toString(day)) dayDate,");
		sql.append("                         toString(toYear(dayDate)) yearStr,");
		sql.append("                         toMonth(dayDate) monthInt,");
		sql.append("                         toYearWeek(dayDate, 3) weekInt,");
		sql.append("                         concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr,");
		sql.append("                         concat(toString(weekInt), '周') AS weekStr,");
		sql.append("                         ").append(period).append(" AS period,");
		sql.append("                         argMax(reg.pgid, reg.receivetime) pgid,");
		sql.append("                         argMax(reg.gameid, reg.receivetime) AS gameid,");
		sql.append("                         argMax(reg.os, reg.receivetime) AS os,");
		sql.append("                         argMax(reg.parentchl, reg.receivetime) parentchl,");
		sql.append("                         argMax(reg.chl, reg.receivetime) chl,");
		sql.append("                         argMax(reg.appchl, reg.receivetime) appchl,");
		sql.append("                         argMax(wpc.manage, reg.receivetime) investor,"); // 投放人
		sql.append("                         IFNULL(argMax(osu.dept_group_id, reg.receivetime),0) userGroupId,"); // 组别
		sql.append("                         argMax(osu.dept_id, reg.receivetime) deptId,"); // 部门
		sql.append("                         argMax(ad.adid, reg.receivetime) adidTmp,");
		sql.append("                         argMax(ad.adname, reg.receivetime) adidName,");
		sql.append("                         IFNULL(argMax(ad.adaccount, reg.receivetime),'') advertiserid,");
		sql.append("                         IFNULL(argMax(ad.ctype, reg.receivetime),0) ctype,");
		sql.append("                         (case when adidTmp is not null THEN adidTmp else '' end) as adid,");
		sql.append("                         ub.kid ubkid,");
		sql.append("                         argMax(reg.kid, reg.receivetime) regkid,");
		sql.append("                         argMax(ub.uuid, reg.receivetime) AS uuid,");
		sql.append("                         argMax(vad.convert_name, reg.receivetime) AS convertName,argMax(vad.deep_convert, reg.receivetime) AS deepConvert,argMax(vad.convert_data_type, reg.receivetime) AS convertDataType,");
		sql.append("                         argMax(ul.uuid, reg.receivetime) AS login_uuid");
		sql.append("                     from user_behavior ub");
		sql.append("                     INNER join odsmysql_wan_game g");
		sql.append("                     on ub.gameid = g.id");
		sql.append("                     INNER join thirty_game_device_reg reg");
		sql.append("                     on ub.uuid = reg.uuid");
		sql.append("                     and g.pgid = reg.pgid");
		sql.append("                     LEFT JOIN v_thirty_ad_device ad");
		sql.append("                     on reg.uuid = ad.uuid");
		sql.append("                     and reg.pgid = ad.pgid");
		sql.append("                     and reg.`day` = ad.`day`");
		sql.append("                     LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                     ON wpc.isdelete = 0");
		sql.append("                     AND wpc.parent_code = reg.parentchl");
		sql.append("                     AND wpc.chncode = reg.chl");
		sql.append("                     LEFT JOIN odsmysql_sys_user osu");
		sql.append("                     ON wpc.manage = osu.user_id");
		sql.append("                     LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                     ON ad.adid = vad.adid");
		sql.append("                     AND ad.ctype = vad.ctype");
		sql.append("                     LEFT JOIN user_login ul");
		sql.append("                     on reg.uuid = ul.uuid");
		sql.append("                     and ub.gameid = ul.gameid");
		sql.append("                     and ub.`day` = ul.`day`");
		sql.append("                     where 1=1");
		sql.append("                     and ub.day>= ").append(rsTime);
		sql.append("                     and ub.day<= ").append(reTime);
		sql.append("                     and reg.day<= ").append(reTime);
		sql.append("                     and ub.receivetime >= reg.receivetime");
		sql.append("                     GROUP BY ub.kid");
		sql.append("                 ) a");
		sql.append("                 where 1=1");
		if (isSys == null || isSys != 1) {
//          sql.append("                     -- 渠道权限");
//          sql.append("                     -- 自己及管理的账号");
			sql.append("                     AND investor IN (").append(userIds).append(")");
		}
//        sql.append("                 -- 筛选条件");
		sql.append(commCond);
		sql.append("                 GROUP BY period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append("             ) t005");
		sql.append("             USING (period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append(" )");
		sql.append("         ) t020");
		sql.append("         USING (period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append(" )");
		sql.append("     ) t100");
		sql.append("     FULL JOIN (");
		sql.append("         SELECT");
		sql.append("             period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("             ").append(queryColumn).append(",");
		}
		sql.append("             cost,");
		sql.append("             rudecost,");
		sql.append("             shownums,");
		sql.append("             clicknums,");
		sql.append("             paydevice,");
		sql.append("             userfee,");
		sql.append("             activesharfee,");
		sql.append("             givemoney");
		sql.append("         FROM (");// --活跃充值
		sql.append("             select");
		sql.append("                 period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("                 ").append(queryColumn).append(",");
		}
		sql.append("                 COUNT(DISTINCT regkid) paydevice,");
		sql.append("                 SUM(fee) userfee,");
		sql.append("                 SUM(sharfee) activesharfee,");
		sql.append("                 SUM(givemoney) givemoney");
		sql.append("             from(");
//        sql.append("              -- 活跃设备充值");
		sql.append("                 select");
		sql.append("                     argMax(reg.day, reg.receivetime) regDay,");
		sql.append("                     argMax(rc.day, reg.receivetime) payday,");
		sql.append("                     payday AS day,");
		sql.append("                     parseDateTimeBestEffort(toString(day)) dayDate,");
		sql.append("                     toString(toYear(dayDate)) yearStr,");
		sql.append("                     toMonth(dayDate) monthInt,");
		sql.append("                     toYearWeek(dayDate, 3) weekInt,");
		sql.append("                     concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr,");
		sql.append("                     concat(toString(weekInt), '周') AS weekStr,");
		sql.append("                     ").append(period).append(" AS period,");
		sql.append("                     argMax(reg.pgid, reg.receivetime) pgid,");
		sql.append("                     argMax(reg.gameid, reg.receivetime) AS gameid,");
		sql.append("                     argMax(reg.os, reg.receivetime) AS os,");
		sql.append("                     argMax(reg.parentchl, reg.receivetime) parentchl,");
		sql.append("                     argMax(reg.chl, reg.receivetime) chl,");
		sql.append("                     argMax(reg.appchl, reg.receivetime) appchl,");
		sql.append("                     argMax(wpc.manage, reg.receivetime) investor,"); // 投放人
		sql.append("                     IFNULL(argMax(osu.dept_group_id, reg.receivetime),0) userGroupId,"); // 组别
		sql.append("                     argMax(osu.dept_id, reg.receivetime) deptId,"); // 部门
		sql.append("                     argMax(ad.adid, reg.receivetime) adidTmp,");
		sql.append("                     argMax(ad.adname, reg.receivetime) adidName,");
		sql.append("                     IFNULL(argMax(ad.adaccount, reg.receivetime),'') advertiserid,");
		sql.append("                     IFNULL(argMax(ad.ctype, reg.receivetime),0) ctype,");
		sql.append("                     (case when adidTmp is not null THEN adidTmp else '' end) as adid,");
		sql.append("                     rc.kid rckid,");
		sql.append("                     argMax(reg.kid, reg.receivetime) regkid,");
		sql.append("                     argMax(rc.uuid, reg.receivetime) AS payuuid,");
		sql.append("                     argMax(rc.fee, reg.receivetime) fee,");
		sql.append("                     argMax(vad.convert_name, reg.receivetime) convertName,argMax(vad.deep_convert, reg.receivetime) deepConvert,argMax(vad.convert_data_type, reg.receivetime) convertDataType,");
		sql.append(" argMax(rc.sharfee,reg.receivetime) sharfee, \n ");
		sql.append("                     argMax(rc.givemoney, reg.receivetime) givemoney");
		sql.append("                 from v_original_user_recharge_share rc");
		sql.append("                 INNER JOIN thirty_game_device_reg reg");
		sql.append("                 on rc.uuid=reg.uuid");
		sql.append("                 and rc.pgid = reg.pgid");
		sql.append("                 LEFT JOIN v_thirty_ad_device ad");
		sql.append("                 on reg.uuid = ad.uuid");
		sql.append("                 and reg.pgid = ad.pgid");
		sql.append("                 and reg.`day` = ad.`day`");
		sql.append("                 LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                 ON wpc.isdelete = 0");
		sql.append("                 AND wpc.parent_code = reg.parentchl");
		sql.append("                 AND wpc.chncode = reg.chl");
		sql.append("                 LEFT JOIN odsmysql_sys_user osu");
		sql.append("                 ON wpc.manage = osu.user_id");
		sql.append("                 LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                 ON ad.adid = vad.adid");
		sql.append("                 AND ad.ctype = vad.ctype");
		sql.append("                 where 1=1");
		sql.append("                 and rc.`day` >= ").append(rsTime);
		sql.append("                 and rc.`day`<= ").append(reTime);
		sql.append("                 AND rc.`day` >= reg.`day`");
		sql.append("                 group by rc.kid");
		sql.append("             ) a");
		sql.append("             WHERE 1=1");
		if (isSys == null || isSys != 1) {
//          sql.append("                     -- 渠道权限");
//          sql.append("                     -- 自己及管理的账号");
			sql.append("                     AND investor IN (").append(userIds).append(")");
		}
//        sql.append("             -- 筛选条件");
		sql.append(commCond);
		sql.append("             GROUP BY period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append("         ) t006");
		sql.append("         FULL JOIN (");
//        sql.append("             -- 计划广告数据");
		sql.append("             SELECT");
		sql.append("                 period,");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append("                 ").append(queryColumn).append(",");
		}
		sql.append("                 SUM(cost) cost,");
		sql.append("                 SUM(rudecost) rudecost,");
		sql.append("                 SUM(adshow) shownums,");
		sql.append("                 sum(click) clicknums");
		sql.append("             from (");
		sql.append("                 SELECT");
		sql.append("                     b.date day,");
		sql.append("                     parseDateTimeBestEffort(toString(day)) dayDate,");
		sql.append("                     toString(toYear(dayDate)) yearStr,");
		sql.append("                     toMonth(dayDate) monthInt,");
		sql.append("                     toYearWeek(dayDate, 3) weekInt,");
		sql.append("                     concat(yearStr, '-', IF(10>monthInt, '0', ''), toString(monthInt), '月') AS monthStr,");
		sql.append("                     concat(toString(weekInt), '周') AS weekStr,");
		sql.append("                     ").append(period).append(" AS period,");
		sql.append("                     IFNULL(g.pgid, 0) pgid,");
		sql.append("                     IFNULL(ap.gameid, 0) gameid,");
		sql.append("                     IFNULL(g.os, 0) os,");
		sql.append("                     (case when ap.adid is not null and ap.adid !='' then ap.parentchl else '-' end) parentchl,");
		sql.append("                     (case when ap.adid is not null and ap.adid !='' then ap.chl else '-' end) chl,");
		sql.append("                     (case when ap.adid is not null and ap.adid !='' then ap.appchl else '-' end) appchl,");
		sql.append("                     osu.user_id investor,"); // 投放人
		sql.append("                     IFNULL(osu.dept_group_id,0) userGroupId,"); // 组别
		sql.append("                     osu.dept_id deptId,"); // 部门
		sql.append("                     IFNULL(b.ad_account,'') advertiserid,"); // 广告账户
		sql.append("                     IFNULL(b.ctype,0) ctype,");
		sql.append("                     b.ad_id adid,");
		sql.append("                     b.ad_name adidName,");
		sql.append("                     b.adconvert adconvert,");
		sql.append("                     b.adshow adshow,");
		sql.append("                     b.click click,");
		sql.append("                     b.rudecost rudecost,");
		sql.append("                     vad.convert_name convertName,vad.deep_convert deepConvert,vad.convert_data_type convertDataType,");
		sql.append("                     b.cost cost");
		sql.append("                 from  v_adid_rebate_day b");
		sql.append("                 left JOIN ad_ptype ap");
		sql.append("                 on ap.adid = b.ad_id");
		sql.append("                 left JOIN v_odsmysql_wan_game g");
		sql.append("                 on ap.gameid=g.id");
		sql.append("                 LEFT JOIN odsmysql_wan_promotion_channel_v3 wpc");
		sql.append("                 ON wpc.isdelete = 0");
		sql.append("                 AND wpc.parent_code = ap.parentchl");
		sql.append("                 AND wpc.chncode = ap.chl");
		sql.append("                 LEFT JOIN odsmysql_ad_account aa");
		sql.append("                 ON aa.advertiser_id = b.ad_account");
		sql.append("                 AND aa.media_code = toString(b.ctype)");  // 类型不一致
		sql.append("                 LEFT JOIN odsmysql_sys_user osu");
		sql.append("                 ON aa.throw_user = toString(osu.user_id)");
		sql.append("                 LEFT JOIN v_odsmysql_adid_new vad");
		sql.append("                 ON b.ad_id = vad.adid");
		sql.append("                 AND b.ctype = vad.ctype");
		sql.append("                 WHERE 1=1");
		sql.append("                 and b.date >= ").append(rsTime);
		sql.append("                 and b.date <= ").append(reTime);
		if (isSys == null || isSys != 1) {
			sql.append("                 AND b.ad_account IN (").append(adAccounts).append(")");
		}
		sql.append("             ) a");
		sql.append("             where 1=1");
		sql.append(commCond);
		sql.append("             GROUP BY period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append("         ) t010");
		sql.append("         USING (period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append(" )");
		sql.append("     ) t030");
		sql.append("     USING (period");
		if (StringUtils.isNotBlank(queryColumn)) {
			sql.append(", ").append(queryColumn);
		}
		sql.append(" )");
		sql.append(" ) t1000");
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("adid")) {
			sql.append("     LEFT JOIN (");
			sql.append("         SELECT");
			sql.append("             t050.adid adidTmp,");
			sql.append("             t050.budget budget,");
			sql.append("             t050.name adidName,");
			sql.append("             t050.campaign_id campaignId,");
			sql.append("             t050.campaign_name campaignName,");
			sql.append("             t050.start_time startTime,");
			sql.append("             t050.end_time endTime,");
			sql.append("             t050.convert_id convertId,");
			sql.append("             t050.create_time createTime,");
			sql.append("             t050.update_time updateTime,");
			sql.append("             t050.union_video_type unionVideoType,");
			sql.append("             t050.inventory_type inventoryType,");
			sql.append("             t050.delivery_range deliveryRange,");
			sql.append("             t050.bid pricing,");
			sql.append("             t050.advertiser_id advertid,");
			sql.append("             t051.name adAccountName,");
			sql.append("             t050.status status,");
			sql.append("             t050.opt_status optStatus,");
			sql.append("             t050.adgroup_id adgroupId,");
			sql.append("             t050.convert_name convertName,");
			sql.append("             t050.convert_descri convertDescri,");
			sql.append("             t050.deep_convert deepConvert,");
			sql.append("             t050.deep_convert_descri deepConvertDescri,");
			sql.append("             t050.ctype pCtype");
			sql.append("         FROM v_odsmysql_adid_new t050");
			sql.append("         LEFT JOIN v_odsmysql_advertiser t051 ON");
			sql.append("         t050.advertiser_id = t051.ad_account ");
			sql.append("         AND t050.ctype = t051.ctype");
			sql.append("     ) t2000");
			sql.append("     ON adid = t2000.adidTmp and t2000.pCtype = ctype");
		}
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("deptId")) {
			sql.append("     LEFT JOIN odsmysql_sys_dept osd ON deptId = osd.dept_id");
		}
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("investor")) {
			sql.append("     LEFT JOIN odsmysql_sys_user osu ON investor = osu.user_id");
		}
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("userGroupId")) {
			sql.append("  	 LEFT JOIN odsmysql_sys_dept_group osdg  ON osdg.id = userGroupId ");
		}
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("advertiserid") && !queryColumn.contains("adid")) {
			sql.append("  	 LEFT JOIN odsmysql_ad_account oaa  ON oaa.advertiser_id = advertiserid  AND oaa.media_code = toString(ctype)");
		}
		sql.append("     WHERE 1=1 ");
		sql.append("     HAVING 1=1 ");
		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("adid")) {
				sql.append(" and  adid ==''  ");
				sql.append(" or adid !=''  ");
				sql.append(" and ctype ==1  ");
				sql.append(" or ctype ==8  ");
			}
			if (queryColumn.contains("advertiserid")) {
				sql.append(" and advertid ==''  ");
				sql.append(" or advertid !=''  ");
				sql.append(" and ctype ==1  ");
				sql.append(" or ctype ==8  ");
			}
		}
		sql.append(" ORDER BY period DESC");
		if (StringUtils.isNotBlank(queryColumn) && queryColumn.contains("adid")) {
			sql.append(" , createTime DESC");
		}
		log.info("sql : [{}]", sql.toString());

		List<AdDataAnalysisVO> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(AdDataAnalysisVO.class));
		return list;
	}

	private String convertQueryColumn(String queryColumn) {
		if (StringUtils.isNotBlank(queryColumn) && (queryColumn.contains("adid") || queryColumn.contains("advertiserid"))) {
			return String.format("%s,%s", queryColumn, "ctype");
		}
		return queryColumn;
	}
}
